package amushen.base;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

import sun.misc.Resource;

/**
 * deal with database
 * 
 * @author shennan
 * 
 */
public class DBUtil {
	private Connection conn;
	private ResultSet rs;
	private PreparedStatement ps;
	
	public DBUtil() {
		try {
			// Load the HSQL Database Engine JDBC driver
			// hsqldb.jar should be in the class path or made part of the
			// current jar
			Class.forName("org.hsqldb.jdbc.JDBCDriver");

			// connect to the database. This will load the db files and start
			// the
			// database if it is not alread running.
			// db_file_name_prefix is used to open or create files that hold the
			// state
			// of the db.
			// It can contain directory names relative to the
			// current working directory
//			conn = DriverManager.getConnection("jdbc:hsqldb:" + "test", // filenames
//					"sa", // username
//					""); // password
			
			conn = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/","sa","");
			conn.setAutoCommit(false);
			
		} catch (Exception e) {
			e.printStackTrace();
			Logger.getLogger(DBUtil.class.getName()).log(Level.SEVERE,
					"Can't open db." + e.getMessage());
		}
	}
	
	
	/**
	 * use for SQL command SELECT
	 * @param sqlKey load sql from sql.properties file
	 * @param params every parameter
	 * @return
	 * @throws SQLException
	 */
	public List query(String sqlKey,Object[] params){
		String sql=SqlHelper.getSQL(sqlKey);
		
		Logger.getLogger(DBUtil.class.getName()).log(Level.INFO,"query:"+sqlKey+","+sql);				
		
		if(sql!=""){
			try{
				ps = conn.prepareStatement(sql);				
				setParameters(ps, params);				
				rs = ps.executeQuery();
				return dump(rs);
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public List dump(ResultSet rs)throws SQLException {

        // the order of the rows in a cursor
        // are implementation dependent unless you use the SQL ORDER statement
        ResultSetMetaData meta   = rs.getMetaData();
        int               colmax = meta.getColumnCount();
        int               i;
        Object            o = null;
        Object[]		  rowdata = null;
        ArrayList 		  ret = new ArrayList();
        
        // the result set is a cursor into the data.  You can only
        // point to one row at a time
        // assume we are pointing to BEFORE the first row
        // rs.next() points to next row and returns true
        // or false if there is no next row, which breaks the loop
        for (; rs.next(); ) {
        	rowdata=new Object[colmax];
        	
            for (i = 0; i < colmax; ++i) {
                rowdata[i]= rs.getObject(i + 1);    // Is SQL the first column is indexed
            }
            ret.add(rowdata);
        }
        return ret;
    }        

	/**
	 * use for SQL commands CREATE, DROP, INSERT and UPDATE
	 * @param sqlKey
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public int update(String sqlKey,Object[] params){
		int i=0;
		try{			
			String sql=SqlHelper.getSQL(sqlKey);
			Logger.getLogger(DBUtil.class.getName()).log(Level.INFO,"update:"+sqlKey+","+sql);
			
			ps=conn.prepareStatement(sql);
			setParameters(ps, params);
			
			i = ps.executeUpdate(); // run the query
	
			if (i == -1) {
				System.out.println("db error : " + SqlHelper.getSQL(sqlKey));
			}			
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}
		return i;
	}
	
	
	private void setParameters(PreparedStatement ps,Object[] paras)throws SQLException{
		if(paras!=null){
			for(int i=1;i<=paras.length;i++){
				Object p=paras[i-1];
				if(p==null)continue;
				if(p instanceof String){
					ps.setString(i, (String)p);
				}else if(p instanceof Integer){
					ps.setInt(i, ((Integer)p).intValue());
				}else if(p instanceof Long){
					ps.setLong(i, ((Long)p).longValue());
				}else if(p instanceof Float){
					ps.setFloat(i, ((Float)p).floatValue());
				}else if(p instanceof Double){
					ps.setDouble(i, ((Double)p).doubleValue());
				}else if(p instanceof Boolean){
					ps.setBoolean(i, ((Boolean)p).booleanValue());						
				}else if(p instanceof java.sql.Date){
					ps.setDate(i, (java.sql.Date)p);
				}
			}
		}
	}
	
	public void commit(){
		if(conn!=null){
			try {
				conn.commit();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void rollback(){
		if(conn!=null){
			try {
				conn.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	//close db
	public void close(){
		try {
			if(rs!=null){
				rs.close();
			}
			if(ps!=null){
				ps.close();
			}
			if(conn!=null){				
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		rs=null;
		ps=null;
		conn=null;
	}
	
	/**
	 * use for load sqls from file
	 * @author shen.n
	 *
	 */
	private static class SqlHelper{
		private static SqlHelper ins=null;
		private Properties sqls=null;
		private SqlHelper(){
			try {
				sqls=Resources.getResourceAsProperties("/amushen/sql.properties");
			} catch (IOException e) {			
				e.printStackTrace();
			}
			
		}
		
		public synchronized static String getSQL(String key){
			if(ins==null){
				ins=new SqlHelper();
			}
			if(ins.sqls==null)return "";
			return ins.sqls.getProperty(key);
		}
		
		
	}
}
